#!/usr/bin/python3
# -*- coding: utf-8 -*-
'模塊注釋'
__author__ = 'Grant Wu'

import cx_Oracle
import pymysql
from impala import dbapi
from impala.hiveserver2 import HiveServer2Connection


def connectOracleDB(database, host='oracle.bi.tec.lcbcorp.com', username='erp', password='uf0000'):
    try:
        link = username + '/' + password + '@' + host + ':' + str(1521) + '/' + database
        conn = cx_Oracle.connect(link)
    except Exception as err:
        raise err
    # cursor=db.cursor()
    return conn


def connectDB(database, host='mysql.lcbint.cn', username='lcb_dev', password='123456', connect_timeout=120):
    try:
        conn = pymysql.connect(host=host, user=username, passwd=password, db=database, charset='utf8',
                               connect_timeout=connect_timeout)
    except Exception as err:
        raise err
    # cursor=db.cursor()
    return conn


def connectTIDB(database, host='lcb-tidb-db.master.lcbint.cn', username='lcb_dev', password='@,ue"L&5WylnK$GRb&ms',port=4000, connect_timeout=120):
    try:
        conn = pymysql.connect(host=host, user=username,passwd=password,port=port, db=database, charset='utf8',
                               connect_timeout=connect_timeout)
    except Exception as err:
        raise err
    # cursor=db.cursor()
    return conn


def connectTIDB(database, host='lcb-tidb-db.master.lcbcorp.com', username='soa-service', password='123456', port=20008,
                connect_timeout=120):
    try:
        conn = pymysql.connect(host=host, user=username, port=port, passwd=password, db=database, charset='utf8',
                               connect_timeout=connect_timeout)
    except Exception as err:
        raise err
    # cursor=db.cursor()
    return conn


# 链接impala，返回的是HiveServer2Connection
def connectImpalaDB(db, host='192.168.6.76', port=21050):
    try:
        conn: HiveServer2Connection = dbapi.connect(host=host, port=21050, database=db, timeout=60000)
    except Exception as err:
        raise err
    # cursor=db.cursor()
    return conn


def closeDB(db):
    db.close()


# con中包含了mysqlHost，mysqlDB, 需要在外边关闭
def exeOracleSelectSql(sql, con: cx_Oracle.Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql)
        return cursor.fetchall()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


def exeOracleSelectSqlWithArgs(sql, args: tuple, con: cx_Oracle.Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql, args)
        return cursor.fetchall()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


def exeOracleSelectSqlWithSchema(sql, con: cx_Oracle.Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql)
        alldata = cursor.fetchall()
        desc = cursor.description
        # print("=-=====desc:{}".format(desc))
        resultData = []
        for data in alldata:
            rowData = {}
            for i in range(0, len(desc)):
                rowData[desc[i][0]] = data[i]
            resultData.append(rowData)
        return resultData
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# 多行一起插入
def exemanySql(params, sql, con: pymysql.Connection):
    cursor = con.cursor()
    affected_rows = 0
    try:
        cursor.executemany(sql, params)
        con.commit()
        affected_rows = cursor.rowcount
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()
        return affected_rows


# 需要在外边关闭con,
# 返回影响的记录数量;
# sql 中的字符串参数需要用spaceholder: '%s', 切记带单引号
def exeSql(sql, con: pymysql.Connection):
    con.ping(reconnect=True)
    cursor = con.cursor()
    result = None
    try:
        result = cursor.execute(sql)
        con.commit()
        return result
        # for item in cursor:
        #     print(item)
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# 需要在外边关闭con;
# sql中需要以%s作为placeholder，对于字符串参数%s不需要带单引号
def exeSqlWithArgs(sql, args: tuple, con: pymysql.Connection):
    con.ping(reconnect=True)
    cursor = con.cursor()
    result = None
    try:
        result = cursor.execute(sql, args)
        con.commit()
        return getLastId(con)
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# 获取左后插入的ID
def getLastId(db):
    cursor = db.cursor()
    try:
        cursor.execute("select last_insert_id()")
        data = cursor.fetchall()
        return data[0][0]
    except Exception as err:
        print("error:", err)
        raise err
    finally:
        cursor.close()


# con中包含了mysqlHost，mysqlDB, 需要在外边关闭
def exeSelectSql(sql, con: pymysql.Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql)
        return cursor.fetchall()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


def exeSelectSqlWithSchema(sql, con: pymysql.Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql)
        alldata = cursor.fetchall()
        desc = cursor.description
        # print("=-=====desc:{}".format(desc))
        resultData = []
        for data in alldata:
            rowData = {}
            for i in range(0, len(desc)):
                rowData[desc[i][0]] = data[i]
            resultData.append(rowData)
        return resultData
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# con中包含了mysqlHost，mysqlDB, 需要在外边关闭
def exeSelectSqlWithArgs(sql, args: tuple, con: pymysql.Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql, args)
        return cursor.fetchall()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# 执行impala ddl语句， 需要在外边关闭con
def exeImpalaSql(sql, con: HiveServer2Connection):
    cursor = con.cursor()
    config = {"QUERY_TIMEOUT_S": 600}
    cursor.execute(sql, parameters=None, configuration=config)
    result = None
    try:
        result = cursor.execute(sql)
        con.commit()
        for item in cursor:
            print(item)
        return result
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# 执行impala ddl语句， 需要在外边关闭con，不返回值
def exeImpalaSql2(sql, con: HiveServer2Connection):
    cursor = con.cursor()
    try:
        cursor.execute(sql)
        con.commit()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# impala多行一起插入
def exeImpalaManySql(params, sql, con: HiveServer2Connection):
    cursor = con.cursor()
    affected_rows = 0
    try:
        cursor.executemany(operation=sql, seq_of_parameters=params)
        con.commit()
        affected_rows = cursor.rowcount
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()
        return affected_rows


# Impala 查询con中包含了ImpalaHost，DB, 需要在外边关闭
def exeImpalaSelectSql(sql, con: HiveServer2Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql)
        return cursor.fetchall()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# Impala 查询con中包含了ImpalaHost，DB, 需要在外边关闭
# 查詢結果帶schema
def exeImpalaSelectSqlWithSchema(sql, con: HiveServer2Connection):
    cursor = con.cursor()
    try:
        result = cursor.execute(sql)
        alldata = cursor.fetchall()
        desc = cursor.description
        # print("=-=====desc:{}".format(desc))
        resultData = []
        for data in alldata:
            rowData = {}
            for i in range(0, len(desc)):
                rowData[desc[i][0]] = data[i]
            resultData.append(rowData)
        return resultData
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# Impala 查询con中包含了ImpalaHost，DB, 需要在外边关闭,添加项目名称
def exeImpalaSelectSqlWithProject(sql, project, con: HiveServer2Connection):
    cursor = con.cursor()
    try:
        sql1 = sql + " /*SA({0})*/".format(project)
        result = cursor.execute(sql1)
        return cursor.fetchall()
    except Exception as err:
        print(err)
        raise err
    finally:
        cursor.close()


# 在同一个Host中备份数据
def backupMysql(con, mysqlDB, table, backupTable):
    ddl_sql = 'create table IF NOT EXISTS %s.%s like %s.%s' % (mysqlDB, backupTable, mysqlDB, table)
    dml_sql = 'insert into %s.%s select * from %s.%s' % (mysqlDB, backupTable, mysqlDB, table)
    print(ddl_sql)
    print(dml_sql)
    result = exeSql(ddl_sql, con)
    print(result)
    num = exeSql(dml_sql, con)
    print(num)


# 在不同Host之间中备份数据， from con1:table1   to con2:table2
# table2需要事先创建好，后面可以增加自动创建的方法
# TODO
def backupMysqlDiffHost(con1: pymysql.Connection, table1, con2: pymysql.Connection, table2):
    olddata_sql = 'select * from %s' % table1
    cursor1 = con1.cursor()
    cursor1.execute(olddata_sql)
    data = cursor1.fetchall()

    cursor2 = con2.cursor()
    # 構造table2 的insert語句
    insertsql = buildInsertSql(con2, table2)
    try:
        # data 为list[tuple],可以放在executemanay直接运行！！！！
        result = cursor2.executemany(insertsql, data)
        print("=======>back data numbers:{}".format(result))
    except Exception as err:
        print(err)
        print('=====>insert sql data failed: ' + insertsql)

    con2.commit()
    cursor1.close()
    cursor2.close()


def getTableSchema(mysqlCon, tableName):
    sql = "select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_COMMENT from information_schema.COLUMNS where TABLE_NAME = '%s'" % tableName;
    print(sql)
    schema = exeSelectSql(sql, mysqlCon)
    return schema


# 構造插入數據sql
# example:
# insert into lcb_rhd_budget_db.rhd_daymanage_xs_order (
#                     store_id,
#                     store_name,
#                     report_month,
#                     report_date,
#                     create_time,
#                     update_time
#                     )values(%s,%s,%s,%s,%s,%s)
def buildInsertSql(mysqlCon, tableName, database=None):
    dbName = database
    if dbName is None:
        dbName = bytes.decode(mysqlCon.db, 'utf8')
    sql = "insert into {}.{}".format(dbName, tableName)
    schema = getTableSchema(mysqlCon, tableName)
    schemas = []
    for tmp in schema:
        schemas.append(tmp[0])
    columnNames = '`,`'.join(schemas)
    columnNames = '`' + columnNames + '`'
    insertsql = sql + ' ( %s ) values ' % (columnNames)
    insertsql = insertsql + ' (' + '%s,' * schemas.__len__()
    insertsql = insertsql[:-1] + ')'
    return insertsql



if __name__ == '__main__':
    # con_dev=connectDB('lcb_car_db')
    con_dev = connectDB('lcb_mars_db')
    # schema=getTableSchema(con,'crawler_dealer')
    # for item in schema:
    #     name=item[0]
    #     type=item[1]
    #     print(name+":"+type)
    # backupMysql(con_dev,'lcb_mars_db','crawler_dealer_compare','crawler_dealer_compare_20190910B')
    # backupMysql(con_dev,'lcb_db','shop_store','shop_store_withbrand')

    # con_slave05=connectDB('lcb_car_db','mysql-slave05.lcbcorp.com','dev_wushumin','P96y27YmbvohJva29Igf')
    # backupMysqlDiffHost(con_slave05,'car_brand_type',con_dev,'car_brand_type')

    con_dev.close()
    # con_slave05.close()